package com.eascs.web.o2o.web.controller;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.inject.Inject;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.eascs.app.dao.PageResult;
import com.eascs.app.exception.ControllerException;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.exception.RuntimeIOException;
import com.eascs.common.util.IoUtil;
import com.eascs.common.util.StringUtil;
import com.eascs.common.util.UrlUtil;
import com.eascs.report.common.entity.ProvinceTeamTotalForm;
import com.eascs.site.view.BaseRestResult;
import com.eascs.site.view.ViewContext;
import com.eascs.tag.MenuTag;
import com.eascs.web.o2o.web.common.BaseMasterViewController;
import com.eascs.web.o2o.web.entity.O2oPlatformRegional;
import com.eascs.web.o2o.web.service.BuildDataService;
import com.eascs.web.o2o.web.service.BuildEmailService;
import com.eascs.web.o2o.web.service.BuildExcelService;
import com.eascs.web.o2o.web.service.ProvinceNameData;
import com.eascs.web.o2o.web.service.ReportStatService;
import com.eascs.web.o2o.web.util.BankRowWriteHandler;

@Controller
@RequestMapping("/ptt")
public class ProvinceTeamTotalController extends BaseMasterViewController {
	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	ReportStatService reportStatService;

	@Autowired
	BuildExcelService buildExcelService;

	@Autowired
	BuildEmailService buildEmailService;

	@Inject
	BuildDataService buildDataService;

	@Autowired
	ProvinceNameData provinceNameData;


	/*
	 * 按省区团队放款量统计
	 */
	
	@RequestMapping(value = "/reportStatByTeam")
	public String ReportStatByTeam(ProvinceTeamTotalForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "teamtotal");
		String o2ourl = "/pttRest/o2oTeam";

		if (entity != null) {
			String province = entity.getProvince() == null ? null : entity.getProvince();
			String auditorName   = entity.getAuditorName() == null ? null : entity.getAuditorName();
			String statisticDate  = entity.getStatisticDate() == null ? "" : entity.getStatisticDate();
			String auditorNameNo   = entity.getAuditorNameNo() == null ? null : entity.getAuditorNameNo();
			String isDimission    = entity.getIsDimission() == null ? "" : entity.getIsDimission();
			String joinDateStart   = entity.getJoinDateStart() == null ? null : entity.getJoinDateStart();
			String joinDateEnd   = entity.getJoinDateEnd() == null ? null : entity.getJoinDateEnd();
			String postCode   = entity.getPostCode() == null ? null : entity.getPostCode();
			String postName   = entity.getPostName() == null ? null : entity.getPostName();
			o2ourl = UrlUtil.appendUrl(o2ourl, "postCode", postCode);
			o2ourl = UrlUtil.appendUrl(o2ourl, "postName", postName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
			o2ourl = UrlUtil.appendUrl(o2ourl, "auditorName", auditorName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "statisticDate", statisticDate);
			o2ourl = UrlUtil.appendUrl(o2ourl, "auditorNameNo", auditorNameNo);
			o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateStart", joinDateStart);
			o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateEnd", joinDateEnd);
			o2ourl = UrlUtil.appendUrl(o2ourl, "isDimission", isDimission);
			o2ourl = o2ourl + "&o2oIndex=";
		} else {
			o2ourl = o2ourl + "?o2oIndex=";
		}

		List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

		ViewContext.put("o2ourl", o2ourl);
		ViewContext.put("vo", entity);
		ViewContext.put("proList", proList);
		return "reportstat/teamTotalList";
	}
	
	
	
	// 省区查询报表下载
	@RequestMapping("/downloadTeam")
	public void downloadTeam(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
		                             HttpServletRequest request) {
			String province = entity.getProvince();
			String auditorName   = entity.getAuditorNameNo();
			String statisticDate  = entity.getStatisticDate();
			entity.setPageSize(Integer.MAX_VALUE);
			PageResult<Object[]> pageResult =reportStatService.getTeamTotalByPageForExcle(entity);
			saveReportStatByTeam(pageResult.getData(), statisticDate, auditorName, province);
			String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
			StringBuffer s       = new StringBuffer();
			String       outPath = s.append(path).append("/teamtotal.xls").toString();
			OutputStream os      = null;
			try {
				String outfile;
				if (province == null) {
					outfile = "省区团队放款量-按省区-" + entity.getStatisticDate() + ".xls";
				} else {
					outfile = entity.getProvince() + "-省区团队放款量-按省区-" + entity.getStatisticDate() + ".xls";
				}
				String userAgent = request.getHeader("User-Agent");
				byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
				outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
				File f = new File(outPath);
				if (!f.exists())
					throw new RuntimeIOException("file not found");
				os = httpServletResponse.getOutputStream();
				httpServletResponse.reset();
				httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
				httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
				ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
				IoUtil.fileToOutputStream(f, byteArrayOutputStream);
				os.write(byteArrayOutputStream.toByteArray());
				os.flush();
			} catch (IOException e) {
				throw new RuntimeIOException(e);
			} finally {
				IoUtil.close(os);
			}
		}

	
		// 先生成文件然后在读这个文件(省区查询报表)
		public String saveReportStatByTeam(List<Object[]> data, String statisticDate, String auditorName,
		                                       String province) {
			// 模板
			String soucreFileName = "/excelTemplate/team_totals.xls";
			// 输出路径
			StringBuffer     s    = new StringBuffer();
            String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
			SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

			String outPath = s.append(path).append("/teamtotal.xls").toString();

			// 包含的列
			int col = 15;
			// 需要统计的列
			int[]  arr = {5,6,7,8,9,10,11,12,13,14};
			String title;

			if (province == null) {
				title = "省区团队放款量-" + statisticDate;
			} else {
				title = province + "-省区团队放款量报表-" + statisticDate;
			}

			// 开始的表格，行，列
			int sheetIndex  = 0;
			int rowIndex    = 2;
			int columnIndex = 0;
			BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
					sheetIndex, rowIndex, columnIndex);

			// 生产excel
			write(soucreFileName, outPath, bankRowWriteHandler);

			return path + "/" + outPath;
		}

		
		
		
		
		

		/*
		 *按经办人放款量
		 */
		
		@RequestMapping(value = "/reportStatByOperator")
		public String ReportStatByOperator(ProvinceTeamTotalForm entity) {
			ViewContext.put(MenuTag.CURRENT_MENU_CODE, "operatortotal");
			String o2ourl = "/pttRest/o2oOperator";

			if (entity != null) {
				String statisticDate  = entity.getStatisticDate() == null ? "" : entity.getStatisticDate();
				String joinDateStart   = entity.getJoinDateStart() == null ? null : entity.getJoinDateStart();
				String joinDateEnd   = entity.getJoinDateEnd() == null ? null : entity.getJoinDateEnd();
				String province      = entity.getProvince() == null ? "" : entity.getProvince();
				String isDimission    = entity.getIsDimission() == null ? "" : entity.getIsDimission();
				String operatorName  = entity.getOperatorName() == null ? "" : entity.getOperatorName();
				String postCode   = entity.getPostCode() == null ? null : entity.getPostCode();
				String postName   = entity.getPostName() == null ? null : entity.getPostName();
				String auditorName   = entity.getAuditorName() == null ? null : entity.getAuditorName();
				String auditorNameNo   = entity.getAuditorNameNo() == null ? null : entity.getAuditorNameNo();
				o2ourl = UrlUtil.appendUrl(o2ourl, "postCode", postCode);
				o2ourl = UrlUtil.appendUrl(o2ourl, "postName", postName);
				o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateStart", joinDateStart);
				o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateEnd", joinDateEnd);
				o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
				o2ourl = UrlUtil.appendUrl(o2ourl, "isDimission", isDimission);
				o2ourl = UrlUtil.appendUrl(o2ourl, "operatorName", operatorName);
				o2ourl = UrlUtil.appendUrl(o2ourl, "statisticDate", statisticDate);
				o2ourl = UrlUtil.appendUrl(o2ourl, "auditorName", auditorName);
				o2ourl = UrlUtil.appendUrl(o2ourl, "auditorNameNo", auditorNameNo);
				o2ourl = o2ourl + "&o2oIndex=";
			} else {
				o2ourl = o2ourl + "?o2oIndex=";
			}

			List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

			ViewContext.put("o2ourl", o2ourl);
			ViewContext.put("vo", entity);
			ViewContext.put("proList", proList);
			return "reportstat/operatorTotalList";
		}
		
		
		
		// 省区查询报表下载
		@RequestMapping("/downloadOperator")
		public void downloadOperator(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
			                             HttpServletRequest request) {
				String province = entity.getProvince();
				String auditorName   = entity.getAuditorNameNo();
				String statisticDate  = entity.getStatisticDate();
				entity.setPageSize(Integer.MAX_VALUE);
				PageResult<Object[]> pageResult =reportStatService.getOperatorTotalByPageForExcle(entity);
				saveReportStatByOperator(pageResult.getData(), statisticDate, auditorName, province);
				String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				StringBuffer s       = new StringBuffer();
				String       outPath = s.append(path).append("/operatortotals.xls").toString();
				OutputStream os      = null;
				try {
					String outfile;
					if (province == null) {
						outfile = "信贷经理放款量-按省区-" + entity.getStatisticDate() + ".xls";
					} else {
						outfile = entity.getProvince() + "-信贷经理放款量-按省区-" + entity.getStatisticDate() + ".xls";
					}
					String userAgent = request.getHeader("User-Agent");
					byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
					outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
					File f = new File(outPath);
					if (!f.exists())
						throw new RuntimeIOException("file not found");
					os = httpServletResponse.getOutputStream();
					httpServletResponse.reset();
					httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
					httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
					ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
					IoUtil.fileToOutputStream(f, byteArrayOutputStream);
					os.write(byteArrayOutputStream.toByteArray());
					os.flush();
				} catch (IOException e) {
					throw new RuntimeIOException(e);
				} finally {
					IoUtil.close(os);
				}
			}

		
		// 先生成文件然后在读这个文件(省区查询报表)
		public String saveReportStatByOperator(List<Object[]> data, String statisticDate, String auditorName,
			                                       String province) {
				// 模板
				String soucreFileName = "/excelTemplate/operator_totals.xls";
				// 输出路径
				StringBuffer     s    = new StringBuffer();
				String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

				String outPath = s.append(path).append("/operatortotals.xls").toString();

				// 包含的列
				int col =16;
				// 需要统计的列
				int[]  arr = { 6,7,8,9,10,11,12,13,14,15};
				String title;

				if (province == null) {
					title = "信贷经理放款量-" + statisticDate;
				} else {
					title = province + "-信贷经理放款量报表-" + statisticDate;
				}

				// 开始的表格，行，列
				int sheetIndex  = 0;
				int rowIndex    = 2;
				int columnIndex = 0;
				BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
						sheetIndex, rowIndex, columnIndex);

				// 生产excel
				write(soucreFileName, outPath, bankRowWriteHandler);

				return path + "/" + outPath;
			}
		
		
		/*
		 * 	按省区放款量
		 */
		
		
		@RequestMapping(value = "/reportStatByProvince")
		public String reportStatByProvince(ProvinceTeamTotalForm entity) {
			ViewContext.put(MenuTag.CURRENT_MENU_CODE, "provincetotal");

			String o2ourl = "/pttRest/o2oProvince";

			if (entity != null) {
				String province = entity.getProvince() == null ? null : entity.getProvince();
				String statisticDate  = entity.getStatisticDate() == null ? "" : entity.getStatisticDate();
				o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
				o2ourl = UrlUtil.appendUrl(o2ourl, "statisticDate", statisticDate);
				o2ourl = o2ourl + "&o2oIndex=";
			} else {
				o2ourl = o2ourl + "?o2oIndex=";
			}

			List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

			ViewContext.put("o2ourl", o2ourl);
			ViewContext.put("vo", entity);
			ViewContext.put("proList", proList);
			return "reportstat/provinceTotalList";
		}
		
		
		
		// 省区查询报表下载
		@RequestMapping("/downloadProvince")
		public void downloadProvince(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
			                             HttpServletRequest request) {
				String province = entity.getProvince();
				String auditorName   = entity.getAuditorNameNo();
				String statisticDate  = entity.getStatisticDate();
				entity.setPageSize(Integer.MAX_VALUE);
				PageResult<Object[]> pageResult =reportStatService.getProvinceTotalByPage(entity);
				saveReportStatByProvince(pageResult.getData(), statisticDate, auditorName, province);
				String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				StringBuffer s       = new StringBuffer();
				String       outPath = s.append(path).append("/provincetotal.xls").toString();
				OutputStream os      = null;
				try {
					String outfile;
					if (province == null) {
						outfile = "省区放款量-按省区-" + entity.getStatisticDate() + ".xls";
					} else {
						outfile = entity.getProvince() + "-省区放款量-按省区-" + entity.getStatisticDate() + ".xls";
					}
					String userAgent = request.getHeader("User-Agent");
					byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
					outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
					File f = new File(outPath);
					if (!f.exists())
						throw new RuntimeIOException("file not found");
					os = httpServletResponse.getOutputStream();
					httpServletResponse.reset();
					httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
					httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
					ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
					IoUtil.fileToOutputStream(f, byteArrayOutputStream);
					os.write(byteArrayOutputStream.toByteArray());
					os.flush();
				} catch (IOException e) {
					throw new RuntimeIOException(e);
				} finally {
					IoUtil.close(os);
				}
			}

		
		// 先生成文件然后在读这个文件(省区查询报表)
		public String saveReportStatByProvince(List<Object[]> data, String statisticDate, String auditorName,
			                                       String province) {
				// 模板
				String soucreFileName = "/excelTemplate/province_totals.xls";
				// 输出路径
				StringBuffer     s    = new StringBuffer();
				String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

				String outPath = s.append(path).append("/provincetotal.xls").toString();

				// 包含的列
				int col = 11;
				// 需要统计的列
				int[]  arr = { 1,2, 3, 4,5,6,7,8,9,10};
				String title;

				if (province == null) {
					title = "省区放款量-" + statisticDate;
				} else {
					title = province + "-省区放款量报表-" + statisticDate;
				}

				// 开始的表格，行，列
				int sheetIndex  = 0;
				int rowIndex    = 2;
				int columnIndex = 0;
				BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
						sheetIndex, rowIndex, columnIndex);

				// 生产excel
				write(soucreFileName, outPath, bankRowWriteHandler);

				return path + "/" + outPath;
			}
		
		
	
	
	
	
	
	
		/*
		 * 按省区团队放款量统计
		 */
		
		@RequestMapping(value = "/reportStatBySectionTeam")
		public String ReportStatBySectionTeam(ProvinceTeamTotalForm entity) {
			ViewContext.put(MenuTag.CURRENT_MENU_CODE, "sectionteamtotal");
			String o2ourl = "/pttRest/o2oSectionTeam";

			if (entity != null) {
				String province = entity.getProvince() == null ? null : entity.getProvince();
				String auditorName   = entity.getAuditorName() == null ? null : entity.getAuditorName();
				String loanDateStart  = entity.getLoanDateStart() == null ? "" : entity.getLoanDateStart();
				String loanDateEnd  = entity.getLoanDateEnd() == null ? "" : entity.getLoanDateEnd();
				String auditorNameNo   = entity.getAuditorNameNo() == null ? null : entity.getAuditorNameNo();
				String isDimission    = entity.getIsDimission() == null ? "" : entity.getIsDimission();
				String joinDateStart   = entity.getJoinDateStart() == null ? null : entity.getJoinDateStart();
				String joinDateEnd   = entity.getJoinDateEnd() == null ? null : entity.getJoinDateEnd();
				String postCode   = entity.getPostCode() == null ? null : entity.getPostCode();
				String postName   = entity.getPostName() == null ? null : entity.getPostName();
				o2ourl = UrlUtil.appendUrl(o2ourl, "postCode", postCode);
				o2ourl = UrlUtil.appendUrl(o2ourl, "postName", postName);
				o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateStart", joinDateStart);
				o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateEnd", joinDateEnd);
				o2ourl = UrlUtil.appendUrl(o2ourl, "isDimission", isDimission);
				o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
				o2ourl = UrlUtil.appendUrl(o2ourl, "auditorName", auditorName);
				o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateStart", loanDateStart);
				o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
				o2ourl = UrlUtil.appendUrl(o2ourl, "auditorNameNo", auditorNameNo);
				o2ourl = o2ourl + "&o2oIndex=";
			} else {
				o2ourl = o2ourl + "?o2oIndex=";
			}

			List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

			ViewContext.put("o2ourl", o2ourl);
			ViewContext.put("vo", entity);
			ViewContext.put("proList", proList);
			return "reportstat/teamSectionTotalList";
		}
		
		
		
		// 省区查询报表下载
		@RequestMapping("/downloadSectionTeam")
		public void downloadSectionTeam(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
			                             HttpServletRequest request) {
				String province = entity.getProvince();
				String auditorName   = entity.getAuditorNameNo();
				String statisticDate  = entity.getLoanDateStart()+"-"+entity.getLoanDateEnd();//区间时间
				entity.setPageSize(Integer.MAX_VALUE);
				PageResult<Object[]> pageResult =reportStatService.getTeamTotalSectionByPageForExcle(entity);
				saveReportStatBySectionTeam(pageResult.getData(), statisticDate, auditorName, province);
				String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				StringBuffer s       = new StringBuffer();
				String       outPath = s.append(path).append("/teamsectiontotal.xls").toString();
				OutputStream os      = null;
				try {
					String outfile;
					if (province == null) {
						outfile = "省区团队放款量-按省区-" + statisticDate + ".xls";
					} else {
						outfile = entity.getProvince() + "-省区团队放款量-按省区-" + statisticDate + ".xls";
					}
					String userAgent = request.getHeader("User-Agent");
					byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
					outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
					File f = new File(outPath);
					if (!f.exists())
						throw new RuntimeIOException("file not found");
					os = httpServletResponse.getOutputStream();
					httpServletResponse.reset();
					httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
					httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
					ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
					IoUtil.fileToOutputStream(f, byteArrayOutputStream);
					os.write(byteArrayOutputStream.toByteArray());
					os.flush();
				} catch (IOException e) {
					throw new RuntimeIOException(e);
				} finally {
					IoUtil.close(os);
				}
			}

		
			// 先生成文件然后在读这个文件(省区查询报表)
			public String saveReportStatBySectionTeam(List<Object[]> data, String statisticDate, String auditorName,
			                                       String province) {
				// 模板
				String soucreFileName = "/excelTemplate/team_section_totals.xls";
				// 输出路径
				StringBuffer     s    = new StringBuffer();
	            String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

				String outPath = s.append(path).append("/teamsectiontotal.xls").toString();

				// 包含的列
				int col = 9;
				// 需要统计的列
				int[]  arr = { 5,6,7,8};
				String title;

				if (province == null) {
					title = "省区团队放款量-" + statisticDate;
				} else {
					title = province + "-省区团队放款量报表-" + statisticDate;
				}

				// 开始的表格，行，列
				int sheetIndex  = 0;
				int rowIndex    = 2;
				int columnIndex = 0;
				BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
						sheetIndex, rowIndex, columnIndex);

				// 生产excel
				write(soucreFileName, outPath, bankRowWriteHandler);

				return path + "/" + outPath;
			}

			
			
			
			
			
			

			/*
			 *按经办人放款量
			 */
			
			@RequestMapping(value = "/reportStatBySectionOperator")
			public String ReportStatBySectionOperator(ProvinceTeamTotalForm entity) {
				ViewContext.put(MenuTag.CURRENT_MENU_CODE, "operatorsectiontotal");
				String o2ourl = "/pttRest/o2oSectionOperator";

				if (entity != null) {
					String loanDateStart = entity.getLoanDateStart() == null ? null : entity.getLoanDateStart();
					String loanDateEnd   = entity.getLoanDateEnd() == null ? null : entity.getLoanDateEnd();
					String joinDateStart   = entity.getJoinDateStart() == null ? null : entity.getJoinDateStart();
					String joinDateEnd   = entity.getJoinDateEnd() == null ? null : entity.getJoinDateEnd();
					String province      = entity.getProvince() == null ? "" : entity.getProvince();
					String isDimission   = entity.getIsDimission() == null ? "" : entity.getIsDimission();
					String operatorName  = entity.getOperatorName() == null ? "" : entity.getOperatorName();
					String postCode   = entity.getPostCode() == null ? null : entity.getPostCode();
					String postName   = entity.getPostName() == null ? null : entity.getPostName();
					String auditorName   = entity.getAuditorName() == null ? null : entity.getAuditorName();
					String auditorNameNo   = entity.getAuditorNameNo() == null ? null : entity.getAuditorNameNo();
					o2ourl = UrlUtil.appendUrl(o2ourl, "postCode", postCode);
					o2ourl = UrlUtil.appendUrl(o2ourl, "postName", postName);
					o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateStart", loanDateStart);
					o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
					o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateStart", joinDateStart);
					o2ourl = UrlUtil.appendUrl(o2ourl, "joinDateEnd", joinDateEnd);
					o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
					o2ourl = UrlUtil.appendUrl(o2ourl, "isDimission", isDimission);
					o2ourl = UrlUtil.appendUrl(o2ourl, "operatorName", operatorName);
					o2ourl = UrlUtil.appendUrl(o2ourl, "auditorName", auditorName);
					o2ourl = UrlUtil.appendUrl(o2ourl, "auditorNameNo", auditorNameNo);
					o2ourl = o2ourl + "&o2oIndex=";
				} else {
					o2ourl = o2ourl + "?o2oIndex=";
				}

				List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

				ViewContext.put("o2ourl", o2ourl);
				ViewContext.put("vo", entity);
				ViewContext.put("proList", proList);
				return "reportstat/operatorSectionTotalList";
			}
			
			
			
			// 省区查询报表下载
			@RequestMapping("/downloadSectionOperator")
			public void downloadSectionOperator(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
				                             HttpServletRequest request) {
					String province = entity.getProvince();
					String auditorName   = entity.getAuditorNameNo();
					String statisticDate  = entity.getLoanDateStart()+"-"+entity.getLoanDateEnd();//区间时间
					entity.setPageSize(Integer.MAX_VALUE);
					PageResult<Object[]> pageResult =reportStatService.getOperatorTotalSectionByPageForExcle(entity);
					saveReportStatBySectionOperator(pageResult.getData(), statisticDate, auditorName, province);
					String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					StringBuffer s       = new StringBuffer();
					String       outPath = s.append(path).append("/operatorsectiontotals.xls").toString();
					OutputStream os      = null;
					try {
						String outfile;
						if (province == null) {
							outfile = "信贷经理放款量-按省区-" + statisticDate + ".xls";
						} else {
							outfile = entity.getProvince() + "-信贷经理放款量-按省区-" + statisticDate + ".xls";
						}
						String userAgent = request.getHeader("User-Agent");
						byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
						outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
						File f = new File(outPath);
						if (!f.exists())
							throw new RuntimeIOException("file not found");
						os = httpServletResponse.getOutputStream();
						httpServletResponse.reset();
						httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
						httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
						ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
						IoUtil.fileToOutputStream(f, byteArrayOutputStream);
						os.write(byteArrayOutputStream.toByteArray());
						os.flush();
					} catch (IOException e) {
						throw new RuntimeIOException(e);
					} finally {
						IoUtil.close(os);
					}
				}

			
			// 先生成文件然后在读这个文件(省区查询报表)
			public String saveReportStatBySectionOperator(List<Object[]> data, String statisticDate, String auditorName,
				                                       String province) {
					// 模板
					String soucreFileName = "/excelTemplate/operator_section_totals.xls";
					// 输出路径
					StringBuffer     s    = new StringBuffer();
					String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

					String outPath = s.append(path).append("/operatorsectiontotals.xls").toString();

					// 包含的列
					int col =10;
					// 需要统计的列
					int[]  arr = {6,7,8,9};
					String title;

					if (province == null) {
						title = "信贷经理放款量-" + statisticDate;
					} else {
						title = province + "-信贷经理放款量报表-" + statisticDate;
					}

					// 开始的表格，行，列
					int sheetIndex  = 0;
					int rowIndex    = 2;
					int columnIndex = 0;
					BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
							sheetIndex, rowIndex, columnIndex);

					// 生产excel
					write(soucreFileName, outPath, bankRowWriteHandler);

					return path + "/" + outPath;
				}
			
			
			/*
			 * 	按省区放款量
			 */
			
			
			@RequestMapping(value = "/reportStatBySectionProvince")
			public String reportStatBySectionProvince(ProvinceTeamTotalForm entity) {
				ViewContext.put(MenuTag.CURRENT_MENU_CODE, "provincesectiontotal");

				String o2ourl = "/pttRest/o2oSectionProvince";

				if (entity != null) {
					String province = entity.getProvince() == null ? null : entity.getProvince();
					String loanDateStart  = entity.getLoanDateStart() == null ? "" : entity.getLoanDateStart();
					String loanDateEnd  = entity.getLoanDateEnd() == null ? "" : entity.getLoanDateEnd();			
					o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
					o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateStart", loanDateStart);
					o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
					o2ourl = o2ourl + "&o2oIndex=";
				} else {
					o2ourl = o2ourl + "?o2oIndex=";
				}

				List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();

				ViewContext.put("o2ourl", o2ourl);
				ViewContext.put("vo", entity);
				ViewContext.put("proList", proList);
				return "reportstat/provinceSctionTotalList";
			}
			
			
			
			// 省区查询报表下载
			@RequestMapping("/downloadSectionProvince")
			public void downloadSectionProvince(ProvinceTeamTotalForm entity, HttpServletResponse httpServletResponse,
				                             HttpServletRequest request) {
					String province = entity.getProvince();
					String auditorName   = entity.getAuditorNameNo();
					String statisticDate  = entity.getLoanDateStart()+"-"+entity.getLoanDateEnd();//区间时间
					entity.setPageSize(Integer.MAX_VALUE);
					PageResult<Object[]> pageResult =reportStatService.getProvinceTotalSectionByPage(entity);
					saveReportStatBySectionProvince(pageResult.getData(), statisticDate, auditorName, province);
					String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					StringBuffer s       = new StringBuffer();
					String       outPath = s.append(path).append("/provincesection.xls").toString();
					OutputStream os      = null;
					try {
						String outfile;
						if (province == null) {
							outfile = "省区放款量-按省区-" + statisticDate + ".xls";
						} else {
							outfile = entity.getProvince() + "-省区放款量-按省区-" + statisticDate + ".xls";
						}
						String userAgent = request.getHeader("User-Agent");
						byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
						outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
						File f = new File(outPath);
						if (!f.exists())
							throw new RuntimeIOException("file not found");
						os = httpServletResponse.getOutputStream();
						httpServletResponse.reset();
						httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
						httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
						ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
						IoUtil.fileToOutputStream(f, byteArrayOutputStream);
						os.write(byteArrayOutputStream.toByteArray());
						os.flush();
					} catch (IOException e) {
						throw new RuntimeIOException(e);
					} finally {
						IoUtil.close(os);
					}
				}

			
			// 先生成文件然后在读这个文件(省区查询报表)
			public String saveReportStatBySectionProvince(List<Object[]> data, String statisticDate, String auditorName,
				                                       String province) {
					// 模板
					String soucreFileName = "/excelTemplate/province_section_totals.xls";
					// 输出路径
					StringBuffer     s    = new StringBuffer();
					String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

					String outPath = s.append(path).append("/provincesection.xls").toString();

					// 包含的列
					int col = 6;
					// 需要统计的列
					int[]  arr = { 1,2, 3,4};
					String title;

					if (province == null) {
						title = "省区放款量-" + statisticDate;
					} else {
						title = province + "-省区放款量报表-" + statisticDate;
					}

					// 开始的表格，行，列
					int sheetIndex  = 0;
					int rowIndex    = 2;
					int columnIndex = 0;
					BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
							sheetIndex, rowIndex, columnIndex);

					// 生产excel
					write(soucreFileName, outPath, bankRowWriteHandler);

					return path + "/" + outPath;
				}
			
			
			
			
			
			@RequestMapping(value = "/reportStatByBankLimit")
			public String ReportStatByBankLimit(ProvinceTeamTotalForm entity) {
				ViewContext.put(MenuTag.CURRENT_MENU_CODE, "provincebanklimit");
				String o2ourl = "/pttRest/o2oBankLimit";

				if (entity != null) {
					String province = entity.getProvince() == null ? null : entity.getProvince();
					String auditorName   = entity.getAuditorName() == null ? null : entity.getAuditorName();
					String statisticDate  = entity.getStatisticDate() == null ? "" : entity.getStatisticDate();
					String auditorNameNo   = entity.getAuditorNameNo() == null ? null : entity.getAuditorNameNo();
					o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
					o2ourl = UrlUtil.appendUrl(o2ourl, "auditorName", auditorName);
					o2ourl = UrlUtil.appendUrl(o2ourl, "statisticDate", statisticDate);
					o2ourl = UrlUtil.appendUrl(o2ourl, "auditorNameNo", auditorNameNo);
					o2ourl = o2ourl + "&o2oIndex=";
				} else {
					o2ourl = o2ourl + "?o2oIndex=";
				}

				ViewContext.put("o2ourl", o2ourl);
				ViewContext.put("vo", entity);
				ViewContext.put("flag", "flag");
				return "reportstat/bankLimit";
			}
			
			
			// 银行额度数据导出
			@RequestMapping("/downloadBankLimit")
			public void downloadBankLimit(ProvinceTeamTotalForm entity,HttpServletResponse httpServletResponse,HttpServletRequest request) {
					entity.setPageSize(Integer.MAX_VALUE);	
					PageResult<Object[]> pageResult =reportStatService.getBankLimitByPage(entity);
					saveBankLimit(pageResult.getData());
					String       path    = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					StringBuffer s       = new StringBuffer();
					String       outPath = s.append(path).append("/provincebankdata.xls").toString();
					OutputStream os      = null;
					try {
						String outfile ="银行申报审查放款金额.xls";
						String userAgent = request.getHeader("User-Agent");
						byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
						outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
						File f = new File(outPath);
						if (!f.exists())
							throw new RuntimeIOException("file not found");
						os = httpServletResponse.getOutputStream();
						httpServletResponse.reset();
						httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
						httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
						ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
						IoUtil.fileToOutputStream(f, byteArrayOutputStream);
						os.write(byteArrayOutputStream.toByteArray());
						os.flush();
					} catch (IOException e) {
						throw new RuntimeIOException(e);
					} finally {
						IoUtil.close(os);
					}
				}

			
			public String saveBankLimit(List<Object[]> data) {
					// 模板
					String soucreFileName = "/excelTemplate/province_bank_data.xls";
					// 输出路径
					StringBuffer     s    = new StringBuffer();
					String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
					SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");

					String outPath = s.append(path).append("/provincebankdata.xls").toString();

					// 包含的列
					int col = 12;
					// 需要统计的列
					int[]  arr = {1,2,3,4,5,6,7,8,9,10,11};
					String title ="银行申报审查节点待放款金额统计（单位：万元）";


					// 开始的表格，行，列
					int sheetIndex  = 0;
					int rowIndex    = 2;
					int columnIndex = 0;
					BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
							sheetIndex, rowIndex, columnIndex);

					// 生产excel
					write(soucreFileName, outPath, bankRowWriteHandler);

					return path + "/" + outPath;
				}
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			/*
			 * 生成excel 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
			 */
			public void write(String soucreFileName, String outPath, BankRowWriteHandler bankRowWriteHandler) {
				IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
				try {
					ExcelApiHelper.writeHandle(new FileOutputStream(outPath),
							BuildExcelService.class.getResourceAsStream(soucreFileName), bankRowWriteHandler);

				} catch (Exception e) {
					logger.error(e.getMessage(), e);
					throw new ControllerException(e);
				}
			}
		
			
			
			
			
			
		@RequestMapping(value="/reportStatBySectionBankreFund")
		public String reportStatBySectionBankreFund(ProvinceTeamTotalForm entity) {
			ViewContext.put(MenuTag.CURRENT_MENU_CODE, "loanForBankreFund");
			String o2ourl = "/pttRest/o2oBankreFund";
			if(entity!=null) {
				String loanDateStart = entity.getLoanDateStart();
				String loanDateEnd = entity.getLoanDateEnd();
				o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateStart", loanDateStart);
				o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
				o2ourl = o2ourl + "&o2oIndex=";
			} else {
				o2ourl = o2ourl + "?o2oIndex=";
			}
			ViewContext.put("o2ourl", o2ourl);
			ViewContext.put("vo", entity);
			return "reportstat/bankreFundSctionTotalList";
		}
	
	
		// 银行额度数据导出
		@RequestMapping("/downloadSectionBankreFund")
		public void downloadSectionBankreFund(ProvinceTeamTotalForm entity,HttpServletResponse httpServletResponse,HttpServletRequest request) {
			String loanDateStart = entity.getLoanDateStart();
			String loanDateEnd = entity.getLoanDateEnd();
			entity.setPageSize(Integer.MAX_VALUE);
			PageResult<Object[]> pageResult = reportStatService.getBankreFundTotalSectionByPage(entity);
			saveBankreFund(pageResult.getData(),loanDateStart,loanDateEnd);
			
			OutputStream os      = null;
			try {
				String outfile = "银行放款量统计.xls";;
				String userAgent = request.getHeader("User-Agent");
				byte[] bytes     = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
				outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
				StringBuffer     s    = new StringBuffer();
				String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
				String           outPath = s.append(path).append("/银行放款量统计.xls").toString();
				File f = new File(outPath);
				if (!f.exists())
					throw new RuntimeIOException("file not found");
				os = httpServletResponse.getOutputStream();
				httpServletResponse.reset();
				httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
				httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
				ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
				IoUtil.fileToOutputStream(f, byteArrayOutputStream);
				os.write(byteArrayOutputStream.toByteArray());
				os.flush();
			} catch (IOException e) {
			throw new RuntimeIOException(e);
			} finally {
				IoUtil.close(os);
			}
		}
	
		public String saveBankreFund(List<Object[]> data,String loanDateStart,String loanDateEnd) {
			// 模板
			String soucreFileName = "/excelTemplate/bankre_fund.xls";
			// 输出路径
			StringBuffer     s    = new StringBuffer();
			String           path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
			SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM");
			
			String outPath = s.append(path).append("/银行放款量统计.xls").toString();

			// 包含的列
			int col = 5;
			// 需要统计的列
			int[]  arr = { 1,2, 3,4};
			String title = loanDateStart+"至"+loanDateEnd+"各银行放款量统计";

			// 开始的表格，行，列
			int sheetIndex  = 0;
			int rowIndex    = 2;
			int columnIndex = 0;
			BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
					sheetIndex, rowIndex, columnIndex);

			// 生产excel
			write(soucreFileName, outPath, bankRowWriteHandler);
			return path + "/" + outPath;
		}
}
